package com.example.dao;

import com.example.bean.Student;
import com.example.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 学生数据访问对象
 */
public class StudentDAO {

    /**
     * 查询所有学生
     */
    public List<Student> findAll() {
        List<Student> students = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT * FROM student ORDER BY id";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setpId(rs.getString("pId"));
                student.setNo(rs.getString("no"));
                student.setName(rs.getString("name"));
                student.setSex(rs.getString("sex"));
                student.setBirthdate(rs.getTimestamp("birthdate"));
                students.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return students;
    }

    /**
     * 根据ID查询学生
     */
    public Student findById(int id) {
        Student student = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT * FROM student WHERE id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                student = new Student();
                student.setId(rs.getInt("id"));
                student.setpId(rs.getString("pId"));
                student.setNo(rs.getString("no"));
                student.setName(rs.getString("name"));
                student.setSex(rs.getString("sex"));
                student.setBirthdate(rs.getTimestamp("birthdate"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return student;
    }

    /**
     * 添加学生
     */
    public boolean add(Student student) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "INSERT INTO student (pId, no, name, sex, birthdate) VALUES (?, ?, ?, ?, ?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, student.getpId());
            pstmt.setString(2, student.getNo());
            pstmt.setString(3, student.getName());
            pstmt.setString(4, student.getSex());
            pstmt.setTimestamp(5, new java.sql.Timestamp(student.getBirthdate().getTime()));

            int rows = pstmt.executeUpdate();
            return rows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DBUtil.close(conn, pstmt);
        }
    }

    /**
     * 更新学生信息
     */
    public boolean update(Student student) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "UPDATE student SET pId = ?, no = ?, name = ?, sex = ?, birthdate = ? WHERE id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, student.getpId());
            pstmt.setString(2, student.getNo());
            pstmt.setString(3, student.getName());
            pstmt.setString(4, student.getSex());
            pstmt.setTimestamp(5, new java.sql.Timestamp(student.getBirthdate().getTime()));
            pstmt.setInt(6, student.getId());

            int rows = pstmt.executeUpdate();
            return rows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DBUtil.close(conn, pstmt);
        }
    }

    /**
     * 删除学生
     */
    public boolean delete(int id) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "DELETE FROM student WHERE id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);

            int rows = pstmt.executeUpdate();
            return rows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DBUtil.close(conn, pstmt);
        }
    }
}

